package Jdbc;

import org.testng.annotations.Test;

import java.sql.*;

public class Jdbc {
    @Test
    public static void test_01() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/books?useSSL=false","root","mysql");
        PreparedStatement ps= conn.prepareStatement("select * from t_book");
        ResultSet re= ps.executeQuery();
        while (re.next()){
            int id= re.getInt("id");
            String title=re.getString("title");
            System.out.println(id+title);
        }
        Utils.close(re,ps,conn);
    }
//    1).连接到数据库（host:localhost user:root password:root database:books）
//            2).新增一条图书数据（id:4 title:西游记 pub_date:1986-01-01 ）
//        3).故意抛出一个异常（模拟代码出现异常）
//            4).新增一条英雄人物数据（name:孙悟空 gender:1 book_id:4）
    @Test
    public static void test_02() throws ClassNotFoundException, SQLException {
        Connection conn=null;
        PreparedStatement ps=null;
        PreparedStatement ps1=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/books?useSSL=false","root","mysql");
            //关闭自动提交事物
            conn.setAutoCommit(false);
            String sql="insert into t_book(id,title,pub_date) values(4,'西游记','1986-01-01')";
            ps= conn.prepareStatement(sql);
            ps.executeUpdate();
            int a=1/0;
            String sql_insert="insert into t_hero(name,gender,book_id) values('孙悟空',1,4)";
            ps1= conn.prepareStatement(sql_insert);
            ps1.executeUpdate();
            //提交事物
            conn.commit();
        }catch (Exception e){
            if (conn != null) {
                conn.rollback();
            }
            e.printStackTrace();
        }finally {
            Utils.close(ps);
            Utils.close(ps1);
            Utils.close(conn);
        }

    }
}
